﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Public;

namespace Dal
{
    public class CategoryAd
    {
        public static DataTable MULTIPLE(string strCon) 
        {
            DataTable dt = new DataTable();
            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_MULTIPLE");
            }
            catch (Exception)
            {
                dt = null;
            }
            return dt;
        }

        public static DataTable MULTIPLE(string strCon, int type)
        {
            DataTable dt = new DataTable();
            SqlParameter[] pars = { new SqlParameter("@AdType", type) };
            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_Type_MULTIPLE", pars);
            }
            catch (Exception)
            {
                dt = null;
            }
            return dt;
        }

        public static DataTable MULTIPLE(string strCon, int catId, int num)
        {
            DataTable dt = new DataTable();
            SqlParameter[] pars = { 
                                      new SqlParameter("@CatId", catId), 
                                      new SqlParameter("@Num", num) 
                                  };
            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_CatID_MULTIPLE", pars);
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }


        public static DataTable MULTIPLE(string strCon,int size,int index,int adType,out int countRow,out int countPage) 
        {
            DataTable dt = new DataTable();
            SqlParameter[] pars = new SqlParameter[5];
            pars[0] = new SqlParameter("@PageSize",SqlDbType.Int);
            pars[0].Value = size;
            pars[1] = new SqlParameter("@PageIndex",SqlDbType.Int);
            pars[1].Value = index;
            pars[2] = new SqlParameter("@AdType",SqlDbType.Int);
            pars[2].Value = adType;
            pars[3] = new SqlParameter("@CountRow",SqlDbType.Int);
            pars[3].Direction = ParameterDirection.Output;
            pars[4] = new SqlParameter("@CountPage",SqlDbType.Int);
            pars[4].Direction = ParameterDirection.Output;
            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CategoryAd_Search_MULTIPLE", pars);
                countRow = System.Convert.ToInt32(pars[3].Value);
                countPage = System.Convert.ToInt32(pars[4].Value);
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }


        public static DataTable Single(string strCon, int type, int catId)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            SqlParameter[] pars = new SqlParameter[2];
            pars[0] = new SqlParameter("@AdType", SqlDbType.Int);
            pars[0].Value = type;
            pars[1] = new SqlParameter("@CatID", SqlDbType.Int);
            pars[1].Value = catId;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_SINGLE_CATID", pars);
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable Single(string strCon, int pkid)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[0].Value = pkid;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_SINGLE_PKID", pars);
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static int Update(string strCon,
                 int txbCatID,
                 int txbAdType,
                 string txbAdTitle,
                 string txbKeywords,
                 string txbDescription,
                 string txbImgURL,
                 string txbAdContent,
                string txbStartDate,
                string txbEndDate,
                 int txbPKID
             )
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[10];
            pars[0] = new SqlParameter("@CatID", SqlDbType.Int);
            pars[0].Value = txbCatID;
            pars[1] = new SqlParameter("@AdType", SqlDbType.Int);
            pars[1].Value = txbAdType;
            pars[2] = new SqlParameter("@AdTitle", SqlDbType.NVarChar);
            pars[2].Value = txbAdTitle;
            pars[3] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[3].Value = txbKeywords;
            pars[4] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[4].Value = txbDescription;
            pars[5] = new SqlParameter("@ImgURL", SqlDbType.NVarChar);
            pars[5].Value = txbImgURL;
            pars[6] = new SqlParameter("@AdContent", SqlDbType.NVarChar);
            pars[6].Value = txbAdContent;
            pars[7] = new SqlParameter("@StartDate", SqlDbType.NVarChar);
            pars[7].Value = txbStartDate;
            pars[8] = new SqlParameter("@EndDate", SqlDbType.NVarChar);
            pars[8].Value = txbEndDate;
            pars[9] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[9].Value = txbPKID;

            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_UPDATE", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }


        public static int Update(string strCon,
                 int txbCatID,
                 int txbAdType,
                 string txbAdTitle,
                 string txbKeywords,
                 string txbDescription,
                string txbLinkURL,
                 string txbImgURL,
                 string txbAdContent,
                string txbStartDate,
                string txbEndDate,
                 int txbPKID
             )
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[11];
            pars[0] = new SqlParameter("@CatID", SqlDbType.Int);
            pars[0].Value = txbCatID;
            pars[1] = new SqlParameter("@AdType", SqlDbType.Int);
            pars[1].Value = txbAdType;
            pars[2] = new SqlParameter("@AdTitle", SqlDbType.NVarChar);
            pars[2].Value = txbAdTitle;
            pars[3] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[3].Value = txbKeywords;
            pars[4] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[4].Value = txbDescription;
            pars[5] = new SqlParameter("@ImgURL", SqlDbType.NVarChar);
            pars[5].Value = txbImgURL;
            pars[6] = new SqlParameter("@AdContent", SqlDbType.NVarChar);
            pars[6].Value = txbAdContent;
            pars[7] = new SqlParameter("@StartDate", SqlDbType.NVarChar);
            pars[7].Value = txbStartDate;
            pars[8] = new SqlParameter("@EndDate", SqlDbType.NVarChar);
            pars[8].Value = txbEndDate;
            pars[9] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[9].Value = txbPKID;
            pars[10] = new SqlParameter("@LinkURL",SqlDbType.NVarChar);
            pars[10].Value = txbLinkURL;

            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_UPDATE", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int Insert(string strCon,
                 int txbCatID,
                 int txbAdType,
                 string txbAdTitle,
                 string txbKeywords,
                 string txbDescription,
                 string txbImgURL,
                 string txbAdContent,
                string txbStartDate,
                string txbEndDate
             )
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[9];
            pars[0] = new SqlParameter("@CatID", SqlDbType.Int);
            pars[0].Value = txbCatID;
            pars[1] = new SqlParameter("@AdType", SqlDbType.Int);
            pars[1].Value = txbAdType;
            pars[2] = new SqlParameter("@AdTitle", SqlDbType.NVarChar);
            pars[2].Value = txbAdTitle;
            pars[3] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[3].Value = txbKeywords;
            pars[4] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[4].Value = txbDescription;
            pars[5] = new SqlParameter("@ImgURL", SqlDbType.NVarChar);
            pars[5].Value = txbImgURL;
            pars[6] = new SqlParameter("@AdContent", SqlDbType.NVarChar);
            pars[6].Value = txbAdContent;
            pars[7] = new SqlParameter("@StartDate", SqlDbType.NVarChar);
            pars[7].Value = txbStartDate;
            pars[8] = new SqlParameter("@EndDate", SqlDbType.NVarChar);
            pars[8].Value = txbEndDate;

            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_INSERT", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int Insert(string strCon,
                 int txbCatID,
                 int txbAdType,
                 string txbAdTitle,
                 string txbKeywords,
                 string txbDescription,
                 string txbLinkURL,
                 string txbImgURL,
                 string txbAdContent,
                string txbStartDate,
                string txbEndDate
             )
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[10];
            pars[0] = new SqlParameter("@CatID", SqlDbType.Int);
            pars[0].Value = txbCatID;
            pars[1] = new SqlParameter("@AdType", SqlDbType.Int);
            pars[1].Value = txbAdType;
            pars[2] = new SqlParameter("@AdTitle", SqlDbType.NVarChar);
            pars[2].Value = txbAdTitle;
            pars[3] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[3].Value = txbKeywords;
            pars[4] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[4].Value = txbDescription;
            pars[5] = new SqlParameter("@ImgURL", SqlDbType.NVarChar);
            pars[5].Value = txbImgURL;
            pars[6] = new SqlParameter("@AdContent", SqlDbType.NVarChar);
            pars[6].Value = txbAdContent;
            pars[7] = new SqlParameter("@StartDate", SqlDbType.NVarChar);
            pars[7].Value = txbStartDate;
            pars[8] = new SqlParameter("@EndDate", SqlDbType.NVarChar);
            pars[8].Value = txbEndDate;
            pars[9] = new SqlParameter("@LinkURL",SqlDbType.NVarChar);
            pars[9].Value = txbLinkURL;

            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CatAd_INSERT", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int DELETE(string strCon,int pkid) 
        {
            int result = 0;
            SqlParameter[] pars = { new SqlParameter("@PKID", pkid) };
            try
            {
                result = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_CategoryAd_DELETE", pars);
            }
            catch (Exception)
            {
                result = 0;
            }
            return result;
        }

        
    }
}
